1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmSuppliersRecord : Form
14 {
15
16 DataTable dtable = new DataTable();
17 SqlConnection con = null;
18 DataSet ds = new DataSet();
19 SqlCommand cmd = null;
20 DataTable dt = new DataTable();
21 ConnectionString cs = new ConnectionString();
22 public frmSuppliersRecord()
23 {
24 InitializeComponent();
25 }
26 public void GetData()
27 {
28 try{
29 con = new SqlConnection(cs.DBConn);
30 con.Open();
31 cmd = new SqlCommand("SELECT RTRIM(SupplierID)as [Supplier ID],RTRIM(Suppliername) as [Supplier Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Supplier order by SupplierName", con);
32 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
33 DataSet myDataSet = new DataSet();
34 myDA.Fill(myDataSet, "Supplier");
35 dataGridView1.DataSource = myDataSet.Tables["Supplier"].DefaultView;
36 con.Close();
37 }
38 catch (Exception ex)
39 {
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
41 }
42 }
43
44
45 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
46 {
47 string strRowNumber = (e.RowIndex + 1).ToString();
48 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
49 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
50 {
51 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
52 }
53 Brush b = SystemBrushes.ControlText;
54 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
55
56 }
57
58
59 private void Button3_Click(object sender, EventArgs e)
60 {
61 if (dataGridView1.DataSource == null)
62 {
63 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
64 return;
65 }
66 int rowsTotal = 0;
67 int colsTotal = 0;
68 int I = 0;
69 int j = 0;
70 int iC = 0;
71 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
72 Excel.Application xlApp = new Excel.Application();
73
74 try
75 {
76 Excel.Workbook excelBook = xlApp.Workbooks.Add();
77 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
78 xlApp.Visible = true;
79
80 rowsTotal = dataGridView1.RowCount;
81 colsTotal = dataGridView1.Columns.Count - 1;
82 var _with1 = excelWorksheet;
83 _with1.Cells.Select();
84 _with1.Cells.Delete();
85 for (iC = 0; iC <= colsTotal; iC++)
86 {
87 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
88 }
89 for (I = 0; I <= rowsTotal - 1; I++)
90 {
91 for (j = 0; j <= colsTotal; j++)
92 {
93 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
94 }
95 }
96 _with1.Rows["1:1"].Font.FontStyle = "Bold";
97 _with1.Rows["1:1"].Font.Size = 12;
98
99 _with1.Cells.Columns.AutoFit();
100 _with1.Cells.Select();
101 _with1.Cells.EntireColumn.AutoFit();
102 _with1.Cells[1, 1].Select();
103 }
104 catch (Exception ex)
105 {
106 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
107 }
108 finally
109 {
110 //RELEASE ALLOACTED RESOURCES
111 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
112 xlApp = null;
113 }
114 }
115
116
117 private void txtSuppliers_TextChanged(object sender, EventArgs e)
118 {
119 try
120 {
121 con = new SqlConnection(cs.DBConn);
122 con.Open();
123 cmd = new SqlCommand("SELECT RTRIM(SupplierID)as [Supplier ID],RTRIM(Suppliername) as [Supplier Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Supplier where SupplierName like '" + txtSuppliers.Text + "%' order by SupplierName", con);
124 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
125 DataSet myDataSet = new DataSet();
126 myDA.Fill(myDataSet, "Supplier");
127 dataGridView1.DataSource = myDataSet.Tables["Supplier"].DefaultView;
128 con.Close();
129 }
130 catch (Exception ex)
131 {
132 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
133 }
134 }
135
136
137 private void button1_Click(object sender, EventArgs e)
138 {
139 try
140 {
141 Cursor = Cursors.WaitCursor;
142 timer1.Enabled = true;
143 rptSuppliers rpt = new rptSuppliers();
144 //The report you created.
145 frmSuppliersReport frm = new frmSuppliersReport();
146 SqlConnection myConnection = default(SqlConnection);
147 SqlCommand MyCommand = new SqlCommand();
148 SqlDataAdapter myDA = new SqlDataAdapter();
149 POS_DBDataSet myDS = new POS_DBDataSet();
150 //The DataSet you created.
151 myConnection = new SqlConnection(cs.DBConn);
152 MyCommand.Connection = myConnection;
153 MyCommand.CommandText = "select * from Supplier Order by SupplierName";
154 MyCommand.CommandType = CommandType.Text;
155 myDA.SelectCommand = MyCommand;
156 myDA.Fill(myDS, "Supplier");
157 rpt.SetDataSource(myDS);
158 frm.crystalReportViewer1.ReportSource = rpt;
159 frm.Show();
160 }
161 catch (Exception ex)
162 {
163 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
164 }
165 }
166
167 private void timer1_Tick(object sender, EventArgs e)
168 {
169
170 Cursor = Cursors.Default;
171 timer1.Enabled = false;
172 }
173
174 private void frmSuppliersRecord_Load(object sender, EventArgs e)
175 {
176 GetData();
177 }
178
179
180 }
181 }
